package Mike.DatabaseConnector;
import java.sql.*;
import java.util.*;

public class ODBC_Connector {
	private String databasename="shopping";
	private int numcolumn =0;
	private int numrow =0;
	private boolean isconnect;
	private Connection maincon;
	private String mainurl;
	private String maindriver;
	public int getNumrow(){
		return numrow;
	}
	public int getNumcolumn(){
		return numcolumn;
	}
	public ODBC_Connector(String DatabaseName){
		databasename=DatabaseName;
	}
	public void connect(){
		//===== change database here =====
		mainurl = "jdbc:odbc:"+databasename;
		// The JDBC driver to use
		maindriver = "sun.jdbc.odbc.JdbcOdbcDriver";
		try {
			// Load the jdbc driver
			Class.forName(maindriver);
		} catch (Exception e) {}
		
	}
	public boolean isConnect(){
		return isconnect;
	}
	public ResultSet getResultSet(String query){
		ResultSet rs=null;
		try {
			maincon = DriverManager.getConnection(mainurl);
			Statement stmt = maincon.createStatement();
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	public void Update(String query){
		Statement stmt;
		try {
			maincon = DriverManager.getConnection(mainurl);
			stmt = maincon.createStatement();
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void close(){
		try {
			maincon.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public ArrayList getResult(String query){
		boolean connecterror=false;
		ArrayList results=new ArrayList();
		String[] result;
//		System.out.println("get Result");
		// Create a JDBC url
		//===== change database here =====
		String url = "jdbc:odbc:"+databasename;
		// The JDBC driver to use
		String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
		// The query to execute on the database
		//String query = "select * from CATEGORIES";
		try {
			// Load the jdbc driver
			Class.forName(driver);
		} catch (Exception e) {}
		
		Connection con;
		try{
			// Use the driver manager to get a connection to the db
			con = DriverManager.getConnection(url);
			connecterror=true;
			// Use the connection to create a statement
			Statement stmt = con.createStatement();
			// Execute a query using the statement
			ResultSet rs = stmt.executeQuery(query);
			// Print the result (row by row)
			ResultSetMetaData rsMetaData = rs.getMetaData();
		    numcolumn = rsMetaData.getColumnCount();
		    
		    int countrow=-1;
		    numrow=0;
		    while (rs.next()) {
		    	countrow++;
		    	result=new String[numcolumn];
		    	for(int i=1;i<=numcolumn;i++){
		    		result[i-1]=rs.getString(i);
		    		//System.out.print(i+" : "+result[i-1]+"     ");
		    	}
		    	//System.out.println();
		    	numrow++;
		    	results.add(result);
			}
		    con.close();
			return results;
		} catch (SQLException e) {
			//System.out.println("Reach Cache");
			if(connecterror==false){
				System.out.println("cannot connect to database");
			}
			return null;
		}
	}
}
